package com.aaa.sbms.dao;

import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

public interface EmpDao {

    /**
     * 查询部门
     * @return
     */
    @Select("select * from depts where deptid!=5")
    List<Map> depts();

    /**
     * 分页查询员工
     * @return
     */
    @Select("<script>" +
            "select u.userid,u.username,u.sex,d.dname,u.telephone,u.idcard,\n" +
            "date_format(u.locktime,'%Y-%m-%d %h:%i:%s') locktime,p.pname,g.gname\n" +
            "from users u\n" +
            "left join depts d on d.deptid=u.deptid\n" +
            "left join positions p on p.pid=u.positionid\n" +
            "left join groups g on g.groupid=u.groupid\n" +
            "left join train t on t.userid=u.userid\n" +
            "<where>" +
            "d.deptid!=5 and p.pid!=2 and t.gradetype=1\n" +
            "<if test='ename!=null'>and u.username like '%${ename}%'</if>" +
            "<if test=\"deptid!=null and deptid!=''\">and u.deptid=#{deptid}</if>" +
            "</where>" +
            "order by u.locktime desc limit #{begin},#{pageSize}" +
            "</script>")
    List<Map> empPage(Map map);

    /**
     * 查询员工总数量
     * @return
     */
    @Select("<script>select count(*) from users u\n" +
            "left join depts d on d.deptid=u.deptid\n" +
            "left join positions p on p.pid=u.positionid\n" +
            "left join groups g on g.groupid=u.groupid\n" +
            "left join train t on t.userid=u.userid\n" +
            "<where>" +
            "d.deptid!=5 and p.pid!=2 and t.gradetype=1\n" +
            "<if test='ename!=null'>and u.username like '%${ename}%'</if>"+
            "<if test=\"deptid!=null and deptid !=''\">and u.deptid =#{deptid}</if>"+
            "</where></script>")
    int empPageCount(Map map);

    /**
     * 分页查询员工打卡信息
     * @param map
     * @return
     */
    @Select("<script>" +
            "select u.userid,u.username,u.sex,d.dname,u.telephone,u.idcard,\n" +
            "date_format(u.signdate,'%Y-%m-%d %h:%i:%s') signdate,p.pname,u.days,u.signday\n" +
            "from users u\n" +
            "left join depts d on d.deptid=u.deptid\n" +
            "left join positions p on p.pid=u.positionid\n" +
            "<where>" +
            "d.deptid!=5 and p.pid!=2\n" +
            "<if test='ename!=null'>and u.username like '%${ename}%'</if>" +
            "<if test=\"deptid!=null and deptid!=''\">and u.deptid=#{deptid}</if>" +
            "</where>" +
            "order by u.locktime desc limit #{begin},#{pageSize}" +
            "</script>")
    List<Map> empSignPage(Map map);

    /**
     * 查询员工打卡总数量
     * @param map
     * @return
     */
    @Select("<script>select count(*) from users u\n" +
            "left join depts d on d.deptid=u.deptid\n" +
            "left join positions p on p.pid=u.positionid\n" +
            "<where>" +
            "d.deptid!=5 and p.pid!=2\n" +
            "<if test='ename!=null'>and u.username like '%${ename}%'</if>"+
            "<if test=\"deptid!=null and deptid !=''\">and u.deptid =#{deptid}</if>"+
            "</where></script>")
    int empSignPageCount(Map map);

    /**
     * 根据员工编号查询该员工信息
     * @param userid
     * @return
     */
    @Select("select u.userid,u.username,u.sex,d.dname,u.telephone,u.idcard,\n" +
            "date_format(u.locktime,'%Y-%m-%d %h:%i:%s') locktime,p.pname,g.gname\n" +
            "from users u\n" +
            "left join depts d on d.deptid=u.deptid\n" +
            "left join positions p on p.pid=u.positionid\n" +
            "left join groups g on g.groupid=u.groupid\n" +
            "where u.userid=#{userid}")
    Map empOne(int userid);
}
